//
//  ViewController.m
//  FMDB数据库
//
//  Created by webplus on 17/10/25.
//  Copyright © 2017年 sanyi. All rights reserved.
//

#import "ViewController.h"
#import <sqlite3.h>
#import <FMDB.h>

@interface ViewController (){
    FMDatabase *fmdb;
}

@end

@implementation ViewController


- (void)viewDidLoad {
    [super viewDidLoad];
    [self fmdbCreate];
    [self fmdbCreateTable];
    [self fmdbInsertData];
    [self fmdbUpdateData];
    [self fmdbDeleteData];
    [self fmdbQueryData];
    [self fmdbMultipleInsert];
}
#pragma mark - 创建数据库
- (void)fmdbCreate{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *cacheDirectory = [paths objectAtIndex:0];
    
    NSLog(@"app_home_lib_cache: %@",cacheDirectory);
    NSString *dataBasePath = [cacheDirectory stringByAppendingPathComponent:@"TESTFMDB.sqlite"];
    
    fmdb = [FMDatabase databaseWithPath:dataBasePath];
    if (![fmdb open]) {
        fmdb = nil;
        NSLog(@"数据库打开失败!");
        return;
    }
    
}
#pragma mark - 创建表
- (void)fmdbCreateTable{
    NSString *sql = [NSString stringWithFormat:@"create table if not exists staff (id integer primary key autoincrement, name text, age integer, sex varchar);"];
    BOOL isCreate = [fmdb executeUpdate:sql];
    if (isCreate) {
        NSLog(@"成功创建员工表!");
    }else{
        NSLog(@"失败了：lastErrorMessage：%@，lastErrorCode：%d",fmdb.lastErrorMessage,fmdb.lastErrorCode);
    }
}

#pragma mark - 插入表
- (void)fmdbInsertData{
    NSString *sql1 = [NSString stringWithFormat:@"insert into staff (name, age, sex) values ('%@', '%@', '%@');",@"小李",@"27",@"男"];
    NSString *sql2 = [NSString stringWithFormat:@"insert into staff (name, age, sex) values ('%@', '%d', '%@');",@"小房",25,@"男"];
    NSString *sql3 =
    @"insert into staff (name, age, sex) values ('小王', '22', 'v');"
    "insert into staff (name, age, sex) values ('小易', '22', 'v');"
    "insert into staff (name, age, sex) values ('小动', '44', 'a');";
    BOOL isInsert1 = [fmdb executeUpdate:sql1];
    if (isInsert1) {
        NSLog(@"插入sql1成功！");
    }else{
        NSLog(@"插入sql1失败了：lastErrorMessage：%@，lastErrorCode：%d",fmdb.lastErrorMessage,fmdb.lastErrorCode);
    }
    BOOL isInsert2 = [fmdb executeUpdate:sql2];
    if (isInsert2) {
        NSLog(@"插入sql2成功！");
    }else{
        NSLog(@"插入sql2失败了：lastErrorMessage：%@，lastErrorCode：%d",fmdb.lastErrorMessage,fmdb.lastErrorCode);
    }
    BOOL isInsert3 = [fmdb executeStatements:sql3];
    if (isInsert3) {
        NSLog(@"插入sql3成功！");
    }else{
        NSLog(@"插入sql2失败了：lastErrorMessage：%@，lastErrorCode：%d",fmdb.lastErrorMessage,fmdb.lastErrorCode);
    }

}

#pragma mark - 修改表
- (void)fmdbUpdateData{
    NSString *sql1 = [NSString stringWithFormat:@"update staff set age='32' where name='小房';"];
    BOOL isUpdate = [fmdb executeStatements:sql1];
    if (isUpdate) {
        NSLog(@"更新sql1成功！");
    }else{
        NSLog(@"更新sql1失败了：lastErrorMessage：%@，lastErrorCode：%d",fmdb.lastErrorMessage,fmdb.lastErrorCode);
    }
}
#pragma mark - 删除表
- (void)fmdbDeleteData{
    NSString *sql = [NSString stringWithFormat:@"delete from staff where name='小李';"];
    BOOL isDelete = [fmdb executeStatements:sql];
    if (isDelete) {
        NSLog(@"删除sql1成功！");
    }else{
        NSLog(@"删除sql1失败了：lastErrorMessage：%@，lastErrorCode：%d",fmdb.lastErrorMessage,fmdb.lastErrorCode);
    }
}

#pragma mark - 查询表
- (void)fmdbQueryData{
    NSString *sql = @"select * from staff;";
    FMResultSet *resultSet = [fmdb executeQuery:sql];
    while ([resultSet next]) {
        NSString *name = [resultSet objectForColumn:@"name"];
        int age = [resultSet intForColumn:@"age"];
        NSString *sex = [resultSet objectForColumn:@"sex"];
        
        NSLog(@"staff员工表: name:%@ age:%d sex:%@",name,age,sex);
    }
}

#pragma mark - 多线程使用（安全的）
- (void)fmdbMultipleInsert{
    NSLog(@"-----------------这里是分割线--------------------");
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *cacheDirectory = [paths objectAtIndex:0];
    
    NSLog(@"app_home_lib_cache: %@",cacheDirectory);
    NSString *dataBasePath = [cacheDirectory stringByAppendingPathComponent:@"TESTFMDB.sqlite"];
    
    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:dataBasePath];
    [queue inDatabase:^(FMDatabase * _Nonnull db) {
        [db executeUpdate:@"insert into staff (name, age, sex) values ('小其', '22', 'nv');"];
        [db executeUpdate:@"insert into staff (name, age, sex) values ('小楼', '24', 'nv');"];
        [db executeUpdate:@"INSERT INTO staff(name) VALUES (?)", @"小黑"];

        FMResultSet *resultSet = [db executeQuery:@"select * from staff"];
        while ([resultSet next]) {
            
            NSString *name = [resultSet objectForColumn:@"name"];
            int age = [resultSet intForColumn:@"age"];
            NSString *sex = [resultSet objectForColumn:@"sex"];
            
            NSLog(@"staff员工表: name:%@ age:%d sex:%@",name,age,sex);
        }
        
    }];
    
    __block BOOL tag = YES;
    [queue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
        tag = [db executeUpdate:@"insert into staff (name, age, sex) values ('小风', '22', 'nv');"];
        tag = [db executeUpdate:@"insert into staff (name, age, sex) values ('小六', '24', '男');"];
        tag = [db executeUpdate:@"INSERT INTO staff(name) VALUES (?)", @"小曲"];
        //如果有错误 返回
        if (!tag)
        {
            *rollback = YES;
             NSLog(@"回滚！");
            return;
        }else{
            NSLog(@"插入完成!");
        }
    }];
    
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}


@end
